#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys, datetime, time
import pandas as pd
import numpy as np
from xpy3lib.utils import db_utils as util
from xpy3lib.XLogger import XLogger
from sicost.config import app_config
from xpy3lib.utils.XDB2_2_PY import XDB2_2_PY
from xpy3lib.utils.XDataFrameUtils import XDataFrameUtils
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.dp_common_job import DP011MJob__df7
from sicost.porting.C3 import C3

from sicost.so.SO4C2 import SO4C2
from sicost.so.SD4C2 import SD4C2

class JHZKEYJob(AbstractDPJob):
    """
    """
    data_type = None

    def __init__(self,
                 p_config=None,
                 p_db_conn_mpp=None,
                 p_db_conn_rds=None,
                 p_db_conn_dbprod7=None,
                 p_unit=None,
                 p_account=None,
                 p_cost_center=None,
                 p_account_period_start=None,
                 p_account_period_end=None,
                 p_data_type=None):
        """

        :param p_config:
        :param p_db_conn_mpp:
        :param p_db_conn_rds:
        :param p_db_conn_dbprod7:
        :param p_unit:
        :param p_account:
        :param p_cost_center_org:
        :param p_cost_center:
        :param p_account_period_start:
        :param p_account_period_end:
        :param p_data_type: data type是 0 D M。分别是 实时、每日、每月
        :param p_wce_org: 成本科目编号
        :param p_wce:
        :param p_cal_type:
        :param p_coef:
        :param p_mon:
        """
        super(JHZKEYJob, self).__init__(p_config=p_config,
                                        p_db_conn_mpp=p_db_conn_mpp,
                                        p_db_conn_rds=p_db_conn_rds,
                                        p_db_conn_dbprod7=p_db_conn_dbprod7,
                                        p_unit=p_unit,
                                        p_account=p_account,
                                        p_cost_center=p_cost_center,
                                        p_account_period_start=p_account_period_start,
                                        p_account_period_end=p_account_period_end)

        self.data_type = p_data_type

        profile = 'dev'
        config = app_config[profile]
        self.db_conn_mpp2 = util.getConnectionDb2(config.DB_HOST_MPP2_DB2,
                                         config.DB_PORT_MPP2_DB2,
                                         config.DB_DBNAME_MPP2_DB2,
                                         config.DB_USER_MPP2_DB2,
                                         config.DB_PASSWORD_MPP2_DB2)
    def do_execute(self):
        """
        """
        self.logger.info('JHZKEYJob.do_execute')

        p_dept = self.cost_center[0:2]
        p_proj_ty = 'NT'

        sql = " SELECT  " \
              " REC_ID  " \
              " ,ACCT AS ACCOUNT " \
              " ,DEPARTMENT_CODE AS FACTORY  " \
              " ,COST_CENTER " \
              " ,COST_CENTER||'00' AS M_CENT  " \
              " ,UNIT_CODE UNIT  " \
              " ,CLASS AS TEAM  " \
              " ,SHIFT  " \
              " ,PRODUCE_TIME AS WORK_TIME  " \
              " ,PRODUCE_START_TIME AS PROCESS_START_TIME  " \
              " ,PRODUCE_END_TIME AS PROCESS_END_TIME  " \
              " ,BYPRODUCT_CODE AS PRODUCT_CODE  " \
              " ,substr(STEELNO,1,8) AS ST_NO  " \
              " ,PROD_COILNO AS MAT_NO  " \
              " ,INPUT_BYPRODUCT_CODE AS DEVO_PRODUCT_CODE  " \
              " ,ENTRY_COILNO AS IN_MAT_NO  " \
              " ,OUTPUT_WT AS WT  " \
              " ,ROUND(ACT_OUTPUT_WT,3) AS MAT_ACT_WT   " \
              " ,INPUT_WT AS IN_WT  " \
              " ,ACT_INPUT_WT AS IN_MAT_WT_AI  " \
              " ,COST_SUBJECT AS WCE  " \
              " ,COST_SUBJECT_ON_AMT AS ACT_N  " \
              " ,CONSUME_PROJ AS CONSUME_ITEM  " \
              " ,CONSUME_PROJ_DESC AS CONSUME_DESC  " \
              " ,CONSUME_PROJ_UNIT AS CONSUME_UNIT  " \
              " ,CONSUME_AMT AS CONSUME_N  " \
              " ,APPTHROWAIMODE AS APP_THROW_AI_MODE  " \
              " ,ANNEAL_CURVE AS DESIGN_ANNEAL_DIAGRAM_CODE  " \
              " ,TRIM_FLAG  " \
              " ,ROUND(MAT_ACT_WIDTH,3) AS MAT_ACT_WIDTH  " \
              " ,ROUND(MAT_ACT_THICK,3) AS MAT_ACT_THICK " \
              " ,ENTRY_MAT_WIDTH AS IN_MAT_WIDTH  " \
              " ,ENTRY_MAT_THICK AS IN_MAT_THICK  " \
              " ,PLAN_NO  " \
              " ,TRIMM_WIDTH AS TRIM_WIDTH  " \
              " ,ENTRY_MAT_INDIA AS IN_MAT_INNER_DIA  " \
              " ,CUST_ORDER_NO  " \
              " ,PICKLING_TRIMMING_FLAG AS PICKL_TRIM_FLAG  " \
              " ,SORT_GRADE_CODE  " \
              " ,COATING_TYPE AS LAYER_TYPE  " \
              " ,MAT_ACT_LEN  " \
              " ,MAT_ACT_AREA  " \
              " ,TOP_COATING_WT AS TOP_COAT_WT  " \
              " ,BOT_COATING_WT AS BOT_COAT_WT  " \
              " ,DATA_TYPE  " \
              " ,REC_REVISOR  " \
              " ,REC_REVISOR_TIME  " \
              " ,TRIM(PRODUCE_TIME) as remark  " \
              " ,PRODUCE_NICK_FLAG AS LAS_NOTCH_FLAG  " \
              " FROM  BGTARAS1.T_ADS_FACT_SICB_DP0103  " \
              " WHERE 1=1 AND COST_SUBJECT_ON_AMT is not null" \
              " and ACCT = '%s' " \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE = '%s' " \
              " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
              " AND LEFT(PRODUCE_TIME,14) < '%s' " \
              " AND DATA_TYPE = '%s' " % (self.account,
                                          self.cost_center,
                                          self.unit,
                                          self.account_period_start,
                                          self.account_period_end,
                                          self.data_type)


        self.logger.info(sql)
        df_tmp1 = XRetryableQuery(p_db_conn=self.db_conn_rds, p_sql=sql, p_max_times=5).redo()
        success = df_tmp1.empty is False
        self.logger.info(success)
        if success is False:
            return

        df_tmp1.columns = df_tmp1.columns.str.upper()
        p_m_cent = self.cost_center + '00'
        self.logger.info(p_m_cent)
        df_SU05 = df_tmp1

        sql = " SELECT " \
              " DISTINCT nvl(a.m_cent,'') || nvl(a.type,'') || nvl(a.item_code,'') as item_id," \
              " a.wce as new_wce,a.m_cent " \
              " from " \
              " M1_JH.WH_JH00_ZMTZB1 a   " \
              " WHERE 1=1   " \
              " and a.TYPE IN('SD','%s') " \
              " AND a.account in '%s' " \
              " and a.M_CENT='%s' " % (p_proj_ty,
                                       self.account,
                                       p_m_cent)
        self.logger.info(sql)
        #df_T1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df_T1 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df_T1.columns = df_T1.columns.str.upper()
        #if df_T1.empty is False:
        #    return

        def __cal_NEW_WCE(x):
            rst = 0
            # WCE 15开头代表主原料
            if str(x.WCE)[0:2] == '15':
                rst = 'C'
            else:
                rst = x.WCE
            return rst

        df_SU05['NEW_WCE'] = df_SU05.apply(lambda x: __cal_NEW_WCE(x), axis=1)
        v = ['NEW_WCE',
             'M_CENT']
        df_SU05A = pd.merge(df_SU05, df_T1, on=v, how='left')
        df_SU05A.drop(['NEW_WCE'], axis=1, inplace=True)

        sql = " select " \
              " b.ACCOUNT," \
              " b.GROUP_CODE," \
              " b.PRDT_CODE_1ST_2ND " \
              " from " \
              " M1_JH.WH_JH00_DTAIT5 b " \
              " where 1=1"
        self.logger.info(sql)
        #df52 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df52 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df52.columns = df52.columns.str.upper()

        def __cal_PRDT_CODE_1ST_2ND(x):
            rst = x.PRODUCT_CODE[0:2]
            return rst

        df_SU05A['PRDT_CODE_1ST_2ND'] = df_SU05A.apply(lambda x: __cal_PRDT_CODE_1ST_2ND(x), axis=1)
        v = ['ACCOUNT', 'PRDT_CODE_1ST_2ND']
        # NOTE df_D5就是刚刚那个大DF(df12). 这个我已经拼接了item_id,然后需要根据item_id去查df11，
        # NOTE df12 ---- df_D5
        df_D5 = pd.merge(df_SU05A, df52, on=v, how='left')
        # TODO 此处强行对QS_CODE, QS_NAME等属性赋值都是不对的， ??????????????
        # TODO Q161机组这么写是没问题的， 别的机组这么写是不对的
        # self.__fill_qs_xxxx(df_D5=df_D5, df_SU05A=df_SU05A)
        df_D5.drop(['REMARK'], axis=1, inplace=True)
        df_D5.drop(['GROUP_CODE'], axis=1, inplace=True)
        df_D5.drop(['PRDT_CODE_1ST_2ND'], axis=1, inplace=True)

        self.logger.info(df_D5)

        df_D5['QS_CODE'] = ''
        df_D5['QS_NAME'] = ''
        df_D5['WIDTH'] = ''
        df_D5['WIDTH_COD'] = ''
        df_D5['THICK'] = ''
        df_D5['THICK_COD'] = ''
        df_D5['IN_WIDTH'] = ''
        df_D5['IN_WIDTH_COD'] = ''
        df_D5['IN_THICK'] = ''
        df_D5['IN_THICK_COD'] = ''
        df_D5['PLAN_NO_C'] = ''
        df_D5['PLAN_NO_COD'] = ''
        df_D5['TRIM_WIDTH_C'] = ''
        df_D5['TRIM_WIDTH_COD'] = ''
        df_D5['IN_MAT_INNER_COD'] = ''
        df_D5['IN_MAT_DIV_CODE'] = ''
        df_D5['IN_MAT_DIV'] = ''

        #XDataFrameUtils.dataframe2excel(p_dataframe=df_D5, p_file_name='df_D5_before_fill_spec.xls')

        # 1. 先取df_D5内有item_id的所有行， 然后对这个子df 去查蓝色图， 填充那四列
        # 2. 再去df_D5内无Item_id的所有行， 在直接把那四列全部写为NONE， 因为前1步骤已经确定了那四列了。
        # NOTE NOTE NOTE
        # df_D5有一列item_id, 有的行可能有item_id值， 有的可能没有值。如果没有值， 新增的4列全部为NAN。 如果有值， 则根据item_id去查出那个蓝色的图
        sql = " SELECT " \
              " FIELD_LG,M_CENT," \
              " SPEC_BZ," \
              " RANGE_N," \
              " RANGE_COD," \
              " PROD1," \
              " LOWER_V," \
              " SUPER_V " \
              " FROM " \
              " M1_JH.WH_JH00_ZSPEC2" \
              " WHERE 1=1 "
        self.logger.info(sql)
        #df_C2 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df_C2 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df_C2.columns = df_C2.columns.str.upper()
        df_C2['PROD1'] = df_C2['PROD1'].fillna('')
        sql = " SELECT * " \
              " FROM M1_JH.WH_JH00_ZSPEC3   " \
              " WHERE 1=1 "
        self.logger.info(sql)
        #df_C3 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df_C3 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df_C3.columns = df_C3.columns.str.upper()
        sql = " SELECT " \
              " a.ITEM_ID," \
              " a.SPEC_BZ, " \
              " a.SPEC_SHOW " \
              " FROM " \
              " M1_JH.WH_JH00_ZSPEC1 as a " \
              " WHERE 1=1 " \
              " AND a.ACCOUNT='%s' " \
              " AND a.USE_JH='Y' " \
              " AND substr(a.ITEM_ID,7,2) IN ('SD','%s','WZ') " \
              " ORDER BY " \
              " a.item_id,a.SPEC_BZ" % (self.account,
                              p_proj_ty)
        #df_C1 = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df_C1 = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df_C1.columns = df_C1.columns.str.upper()
        df_D5['ITEM_ID'] = df_D5['ITEM_ID'].fillna('null')
        #df_D5_NOID = df_D5[~df_D5['ITEM_ID'].isin(['null'])]
        #df_D5 = df_D5[df_D5['ITEM_ID'].isin(['null'])]
        df_D5_ID = df_D5[df_D5['ITEM_ID'] != 'null']
        df_D5_NOID = df_D5[df_D5['ITEM_ID'] == 'null']
        df_D5_ID = df_D5_ID.reset_index()
        df_D5_NOID = df_D5_NOID.reset_index()

        total_cost = 0
        start_0 = time.mktime(datetime.datetime.now().timetuple()) * 1000
        for index_d5, row_d5 in df_D5_ID.iterrows():
            tmp_item_id = df_D5_ID.loc[index_d5, 'ITEM_ID']
            #if tmp_item_id is None:
            #    continue
            self.logger.info(index_d5)

            # NOTE 如果有值， 则根据item_id去查出那个蓝色的图df_blue
            #success, df_blue = self.__query_blue_pic(p_item_id=tmp_item_id, p_proj_ty=p_proj_ty)
            df_blue = df_C1[df_C1['ITEM_ID'] == tmp_item_id]

            start = time.mktime(datetime.datetime.now().timetuple()) * 1000

            # NOTE 先在C3找一圈， 再去range表里找。 如果都没有， 则不填。
            for index_blue, row_blue in df_blue.iterrows():
                #success, df_c3 = self.__query_ec3_by(p_item_id=tmp_item_id, p_spec=row_blue['SPEC_BZ'])
                df_C3_t = df_C3[
                    (df_C3['ITEM_ID'] == tmp_item_id) & (df_C3['SPEC'] == row_blue['SPEC_BZ'])]

                success = df_C3_t.empty is False
                self.logger.info(success)
                if success is False:
                    continue

                df_C3_t = df_C3_t.reset_index()
                for index_c3, row_c3 in df_C3_t.iterrows():
                    c3 = C3(p_row=row_c3)
                    targeted, spec_value, spec_show_value = self.__cal_spec_via_c3(x=row_d5, p_c3=c3)
                    if targeted:
                        df_D5_ID.loc[index_d5, c3.spec] = spec_value
                        df_D5_ID.loc[index_d5, c3.spec_show] = spec_show_value
                        break

            elapsed = time.mktime(datetime.datetime.now().timetuple()) * 1000 - start
            self.logger.info("Time Used 4 loop-1-A ----->>>> %d -- %f ms" % (index_d5, elapsed))
            start = time.mktime(datetime.datetime.now().timetuple()) * 1000

            # (null)	TRIM_WIDTH_COD
            # NOTE 再去c2里找
            start_b_1 = time.mktime(datetime.datetime.now().timetuple()) * 1000
            tmp_qs_code = df_D5_ID.loc[index_d5, 'QS_CODE']
            self.__fill_spec_values_4_df_D5_via_c2(p_df_D5=df_D5_ID, p_row_d5=row_d5, p_index=index_d5, p_df_blue=df_blue, p_tmp_qs_code=tmp_qs_code, p_df_C2=df_C2)
            elapsed = time.mktime(datetime.datetime.now().timetuple()) * 1000 - start_b_1
            self.logger.info("Time Used 4 loop-1-B-1 ----->>>> %d -- %f ms" % (index_d5, elapsed))
            total_cost += elapsed

            # NOTE 这个job就是为了要这两列(JHZKEY JHZKEY_NAME). 6个拼在一起就是这个
            start_b_2 = time.mktime(datetime.datetime.now().timetuple()) * 1000
            spec_bz_list = df_blue['SPEC_BZ']
            spec_value_list = [df_D5_ID.loc[index_d5, str(i)] for i in spec_bz_list]
            df_D5_ID.loc[index_d5, 'JHZ_KEY_NAME'] = ','.join(spec_bz_list)
            df_D5_ID.loc[index_d5, 'JHZ_KEY'] = ''.join(spec_value_list)
            elapsed = time.mktime(datetime.datetime.now().timetuple()) * 1000 - start_b_2
            self.logger.info("Time Used 4 loop-1-B-2 ----->>>> %d -- %f ms" % (index_d5, elapsed))

            elapsed = time.mktime(datetime.datetime.now().timetuple()) * 1000 - start
            self.logger.info("Time Used 4 loop-1-B ----->>>> %d -- %f ms" % (index_d5, elapsed))


        elapsed = time.mktime(datetime.datetime.now().timetuple()) * 1000 - start_0
        self.logger.info("Time Used 4 loop-1-ALL ----->>>> %f s" % (elapsed/1000))

        self.logger.info("Time Used 4 loop-1-fill_spec_values_4_df_D5_via_c2-total_cost ----->>>> %f s" % (total_cost))

        #self.logger.info(df_D5)
        sql = " DELETE FROM BGTARAS1.T_ADS_FACT_SICB_DP0103 " \
              " WHERE 1=1 " \
              " AND LEFT(PRODUCE_TIME,14) >= '%s' " \
              " AND LEFT(PRODUCE_TIME,14) < '%s' " \
              " AND ACCT = '%s' " \
              " AND COST_CENTER = '%s' " \
              " AND UNIT_CODE='%s' " \
              " AND DATA_TYPE = '%s'" % (
                  self.account_period_start, self.account_period_end, self.account, self.cost_center, self.unit, self.data_type)
        self.logger.info(sql)
        self.db_conn_rds.execute(sql)

        # 写库
        # 数据库dbprod7
        # 将df54写入到BGRAGGCB.SU_AJBG_DP0102
        # 将df9写入到BGRAGGCB.SU_AJBG_DP0102
        df_D5_NOID['ITEM_ID'] = ''
        df_D5_NOID['JHZ_KEY'] = ''
        df_D5_NOID['JHZ_KEY_NAME'] = ''
        df_D5 = pd.concat([df_D5_ID, df_D5_NOID], ignore_index=True)
        df_D5.drop(['index'], axis=1, inplace=True)

        df_D5.rename(columns={'DEVO_PRODUCT_CODE': 'IN_PRODUCT_CODE'}, inplace=True)
        df_D5.rename(columns={'MAT_ACT_WT': 'ACT_WT'}, inplace=True)
        df_D5.rename(columns={'IN_MAT_WT_AI': 'ACT_IN_WT'}, inplace=True)
        #df_D5.rename(columns={'LAYER_THICK_T': 'TOP_PLATE_WT'}, inplace=True)
        #df_D5.rename(columns={'LAYER_THICK_B': 'BOT_PLATE_WT'}, inplace=True)
        df_D5.drop(['M_CENT'], axis=1, inplace=True)

        df_D5.drop(['REC_ID'], axis=1, inplace=True)
        df_D5['REC_CREATOR'] = df_D5['REC_REVISOR']
        df_D5['REC_CREATE_TIME'] = df_D5['REC_REVISOR_TIME']
        df_D5.rename(columns={'ACCOUNT': 'ACCT'}, inplace=True)
        df_D5.rename(columns={'FACTORY': 'DEPARTMENT_CODE'}, inplace=True)
        df_D5.rename(columns={'UNIT': 'UNIT_CODE'}, inplace=True)
        df_D5.rename(columns={'TEAM': 'CLASS'}, inplace=True)
        df_D5.rename(columns={'WORK_TIME': 'PRODUCE_TIME'}, inplace=True)
        df_D5.rename(columns={'PROCESS_START_TIME': 'PRODUCE_START_TIME'}, inplace=True)
        df_D5.rename(columns={'PROCESS_END_TIME': 'PRODUCE_END_TIME'}, inplace=True)
        df_D5.rename(columns={'PRODUCT_CODE': 'BYPRODUCT_CODE'}, inplace=True)
        df_D5.rename(columns={'ST_NO': 'STEELNO'}, inplace=True)
        df_D5.rename(columns={'MAT_NO': 'PROD_COILNO'}, inplace=True)
        df_D5.rename(columns={'IN_PRODUCT_CODE': 'INPUT_BYPRODUCT_CODE'}, inplace=True)
        df_D5.rename(columns={'IN_MAT_NO': 'ENTRY_COILNO'}, inplace=True)
        df_D5.rename(columns={'WT': 'OUTPUT_WT'}, inplace=True)
        df_D5.rename(columns={'ACT_WT': 'ACT_OUTPUT_WT'}, inplace=True)
        df_D5.rename(columns={'IN_WT': 'INPUT_WT'}, inplace=True)
        df_D5.rename(columns={'ACT_IN_WT': 'ACT_INPUT_WT'}, inplace=True)
        df_D5.rename(columns={'WCE': 'COST_SUBJECT'}, inplace=True)
        df_D5.rename(columns={'ACT_N': 'COST_SUBJECT_ON_AMT'}, inplace=True)
        # df3.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df_D5.rename(columns={'CONSUME_ITEM': 'CONSUME_PROJ'}, inplace=True)
        df_D5.rename(columns={'CONSUME_DESC': 'CONSUME_PROJ_DESC'}, inplace=True)
        df_D5.rename(columns={'CONSUME_UNIT': 'CONSUME_PROJ_UNIT'}, inplace=True)
        df_D5.rename(columns={'CONSUME_N': 'CONSUME_AMT'}, inplace=True)
        df_D5.rename(columns={'APP_THROW_AI_MODE': 'APPTHROWAIMODE'}, inplace=True)
        df_D5.rename(columns={'DESIGN_ANNEAL_DIAGRAM_CODE': 'ANNEAL_CURVE'}, inplace=True)
        df_D5.rename(columns={'IN_MAT_WIDTH': 'ENTRY_MAT_WIDTH'}, inplace=True)
        df_D5.rename(columns={'IN_MAT_THICK': 'ENTRY_MAT_THICK'}, inplace=True)
        df_D5.rename(columns={'TRIM_WIDTH': 'TRIMM_WIDTH'}, inplace=True)
        df_D5.rename(columns={'IN_MAT_INNER_DIA': 'ENTRY_MAT_INDIA'}, inplace=True)
        df_D5.rename(columns={'PICKL_TRIM_FLAG': 'PICKLING_TRIMMING_FLAG'}, inplace=True)
        df_D5.rename(columns={'LAYER_TYPE': 'COATING_TYPE'}, inplace=True)
        df_D5.rename(columns={'TOP_COAT_WT': 'TOP_COATING_WT'}, inplace=True)
        df_D5.rename(columns={'BOT_COAT_WT': 'BOT_COATING_WT'}, inplace=True)
        df_D5.rename(columns={'LAS_NOTCH_FLAG': 'PRODUCE_NICK_FLAG'}, inplace=True)
        df_D5.rename(columns={'JHZ_KEY': 'PLAN_SYS_KV_CODE'}, inplace=True)
        df_D5.rename(columns={'JHZ_KEY_NAME': 'PLAN_SYS_KV_CODE_NAME'}, inplace=True)
        df_D5.rename(columns={'CONSUME': 'UNITCONSUME'}, inplace=True)
        df_D5.rename(columns={'WIDTH_COD': 'WIDTH_CODE'}, inplace=True)
        df_D5.rename(columns={'THICK_COD': 'THICK_CODE'}, inplace=True)
        df_D5.rename(columns={'IN_WIDTH': 'ENTRY_WIDTH'}, inplace=True)
        df_D5.rename(columns={'IN_WIDTH_COD': 'ENTRY_WIDTH_CODE'}, inplace=True)
        df_D5.rename(columns={'IN_THICK': 'ENTRY_THICK'}, inplace=True)
        df_D5.rename(columns={'IN_THICK_COD': 'ENTRY_THICK_CODE'}, inplace=True)
        df_D5.rename(columns={'PLAN_NO_COD': 'PLAN_NO_CODE'}, inplace=True)
        df_D5.rename(columns={'TRIM_WIDTH_C': 'TRIMMING_WIDTH_RANGE'}, inplace=True)
        df_D5.rename(columns={'TRIM_WIDTH_COD': 'TRIMMING_WIDTH_CODE'}, inplace=True)
        df_D5.rename(columns={'QS_CODE': 'PROD_CAT_CODE'}, inplace=True)
        df_D5.rename(columns={'QS_NAME': 'PROD_CAT'}, inplace=True)
        #df_D5.rename(columns={'IN_MAT_INNER_COD': 'IN_MAT_DIV_CODE'}, inplace=True)
        df_D5.drop(['IN_MAT_INNER_COD'], axis=1, inplace=True)

        # 更新所有的null为空字符串 NOTE 16列不允许出现nan, 如果是nan则默认为空字符串，
        valid_columns = ['WIDTH', 'WIDTH_CODE', 'THICK', 'THICK_CODE', 'ENTRY_WIDTH', 'ENTRY_WIDTH_CODE', 'ENTRY_THICK',
                         'ENTRY_THICK_CODE', 'PLAN_NO_C', 'PLAN_NO_CODE', 'TRIMMING_WIDTH_RANGE', 'TRIMMING_WIDTH_CODE',
                         'IN_MAT_DIV_CODE', 'IN_MAT_DIV', 'PROD_CAT_CODE', 'PROD_CAT']
        for c in valid_columns:
            df_D5[c].fillna(value='', inplace=True)

        df_D5_15 = df_D5[df_D5['COST_SUBJECT'].str[0:2] == '15']
        df_D5_N15 = df_D5[df_D5['COST_SUBJECT'].str[0:2] != '15']
        success = df_D5_15.empty is False
        if success is False:
            return
        
        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df_D5_15, p_max_times = 5).redo()

        success = df_D5_N15.empty is False
        if success is False:
            return


        v = ['PRODUCE_TIME',
             'OUTPUT_WT',
             'APPTHROWAIMODE',
             'ENTRY_COILNO',
             'PROD_COILNO',
             'WIDTH', 'WIDTH_CODE',
             'THICK', 'THICK_CODE',
             'ENTRY_WIDTH', 'ENTRY_WIDTH_CODE',
             'ENTRY_THICK', 'ENTRY_THICK_CODE',
             'PLAN_NO_C', 'PLAN_NO_CODE',
             'TRIMMING_WIDTH_RANGE', 'TRIMMING_WIDTH_CODE',
             'IN_MAT_DIV_CODE', 'IN_MAT_DIV',
             'PROD_CAT_CODE', 'PROD_CAT']
        df_D5_15.drop_duplicates(subset=v, keep='first', inplace=True)
        df_D5_15.drop(['ACCT'], axis=1, inplace=True)
        df_D5_15.drop(['DEPARTMENT_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['COST_CENTER'], axis=1, inplace=True)
        df_D5_15.drop(['UNIT_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['CLASS'], axis=1, inplace=True)
        df_D5_15.drop(['SHIFT'], axis=1, inplace=True)
        df_D5_15.drop(['PRODUCE_START_TIME'], axis=1, inplace=True)
        df_D5_15.drop(['PRODUCE_END_TIME'], axis=1, inplace=True)
        df_D5_15.drop(['BYPRODUCT_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['STEELNO'], axis=1, inplace=True)
        df_D5_15.drop(['ITEM_ID'], axis=1, inplace=True)
        df_D5_15.drop(['PLAN_SYS_KV_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['PLAN_SYS_KV_CODE_NAME'], axis=1, inplace=True)
        df_D5_15.drop(['INPUT_BYPRODUCT_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['ACT_OUTPUT_WT'], axis=1, inplace=True)
        df_D5_15.drop(['INPUT_WT'], axis=1, inplace=True)
        df_D5_15.drop(['ACT_INPUT_WT'], axis=1, inplace=True)
        df_D5_15.drop(['COST_SUBJECT'], axis=1, inplace=True)
        df_D5_15.drop(['COST_SUBJECT_ON_AMT'], axis=1, inplace=True)
        #df_D5_15.drop(['UNITCONSUME'], axis=1, inplace=True)
        df_D5_15.drop(['CONSUME_PROJ'], axis=1, inplace=True)
        df_D5_15.drop(['CONSUME_PROJ_DESC'], axis=1, inplace=True)
        df_D5_15.drop(['CONSUME_PROJ_UNIT'], axis=1, inplace=True)
        df_D5_15.drop(['CONSUME_AMT'], axis=1, inplace=True)
        df_D5_15.drop(['ANNEAL_CURVE'], axis=1, inplace=True)
        df_D5_15.drop(['TRIM_FLAG'], axis=1, inplace=True)
        df_D5_15.drop(['MAT_ACT_WIDTH'], axis=1, inplace=True)
        df_D5_15.drop(['MAT_ACT_THICK'], axis=1, inplace=True)
        df_D5_15.drop(['ENTRY_MAT_WIDTH'], axis=1, inplace=True)
        df_D5_15.drop(['ENTRY_MAT_THICK'], axis=1, inplace=True)
        df_D5_15.drop(['PLAN_NO'], axis=1, inplace=True)
        df_D5_15.drop(['TRIMM_WIDTH'], axis=1, inplace=True)
        df_D5_15.drop(['ENTRY_MAT_INDIA'], axis=1, inplace=True)
        df_D5_15.drop(['CUST_ORDER_NO'], axis=1, inplace=True)
        df_D5_15.drop(['PICKLING_TRIMMING_FLAG'], axis=1, inplace=True)
        df_D5_15.drop(['SORT_GRADE_CODE'], axis=1, inplace=True)
        df_D5_15.drop(['COATING_TYPE'], axis=1, inplace=True)
        df_D5_15.drop(['MAT_ACT_LEN'], axis=1, inplace=True)
        df_D5_15.drop(['MAT_ACT_AREA'], axis=1, inplace=True)
        df_D5_15.drop(['TOP_COATING_WT'], axis=1, inplace=True)
        df_D5_15.drop(['BOT_COATING_WT'], axis=1, inplace=True)
        df_D5_15.drop(['PRODUCE_NICK_FLAG'], axis=1, inplace=True)
        df_D5_15.drop(['DATA_TYPE'], axis=1, inplace=True)
        df_D5_15.drop(['REC_REVISOR'], axis=1, inplace=True)
        df_D5_15.drop(['REC_REVISOR_TIME'], axis=1, inplace=True)
        df_D5_15.drop(['REC_CREATOR'], axis=1, inplace=True)
        df_D5_15.drop(['REC_CREATE_TIME'], axis=1, inplace=True)
        df_D5_15.rename(columns={'WIDTH': 'WIDTH_15'}, inplace=True)
        df_D5_15.rename(columns={'WIDTH_CODE': 'WIDTH_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'THICK': 'THICK_15'}, inplace=True)
        df_D5_15.rename(columns={'THICK_CODE': 'THICK_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'ENTRY_WIDTH': 'ENTRY_WIDTH_15'}, inplace=True)
        df_D5_15.rename(columns={'ENTRY_WIDTH_CODE': 'ENTRY_WIDTH_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'ENTRY_THICK': 'ENTRY_THICK_15'}, inplace=True)
        df_D5_15.rename(columns={'ENTRY_THICK_CODE': 'ENTRY_THICK_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'PLAN_NO_C': 'PLAN_NO_C_15'}, inplace=True)
        df_D5_15.rename(columns={'PLAN_NO_CODE': 'PLAN_NO_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'TRIMMING_WIDTH_RANGE': 'TRIMMING_WIDTH_RANGE_15'}, inplace=True)
        df_D5_15.rename(columns={'TRIMMING_WIDTH_CODE': 'TRIMMING_WIDTH_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'IN_MAT_DIV_CODE': 'IN_MAT_DIV_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'IN_MAT_DIV': 'IN_MAT_DIV_15'}, inplace=True)
        df_D5_15.rename(columns={'PROD_CAT_CODE': 'PROD_CAT_CODE_15'}, inplace=True)
        df_D5_15.rename(columns={'PROD_CAT': 'PROD_CAT_15'}, inplace=True)

        df_D5_N15.rename(columns={'WIDTH': 'WIDTH_N15'}, inplace=True)
        df_D5_N15.rename(columns={'WIDTH_CODE': 'WIDTH_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'THICK': 'THICK_N15'}, inplace=True)
        df_D5_N15.rename(columns={'THICK_CODE': 'THICK_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'ENTRY_WIDTH': 'ENTRY_WIDTH_N15'}, inplace=True)
        df_D5_N15.rename(columns={'ENTRY_WIDTH_CODE': 'ENTRY_WIDTH_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'ENTRY_THICK': 'ENTRY_THICK_N15'}, inplace=True)
        df_D5_N15.rename(columns={'ENTRY_THICK_CODE': 'ENTRY_THICK_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'PLAN_NO_C': 'PLAN_NO_C_N15'}, inplace=True)
        df_D5_N15.rename(columns={'PLAN_NO_CODE': 'PLAN_NO_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'TRIMMING_WIDTH_RANGE': 'TRIMMING_WIDTH_RANGE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'TRIMMING_WIDTH_CODE': 'TRIMMING_WIDTH_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'IN_MAT_DIV_CODE': 'IN_MAT_DIV_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'IN_MAT_DIV': 'IN_MAT_DIV_N15'}, inplace=True)
        df_D5_N15.rename(columns={'PROD_CAT_CODE': 'PROD_CAT_CODE_N15'}, inplace=True)
        df_D5_N15.rename(columns={'PROD_CAT': 'PROD_CAT_N15'}, inplace=True)


        w = ['PRODUCE_TIME',
             'OUTPUT_WT',
             'APPTHROWAIMODE',
             'ENTRY_COILNO',
             'PROD_COILNO']
        df_D5_N15_NEW = pd.merge(df_D5_N15, df_D5_15, on=w, how='left')
        def __cal_WIDTH(x):

            if x.WIDTH_N15 == '':
                rst = x.WIDTH_15
            else:
                rst = x.WIDTH_N15
            return rst

        df_D5_N15_NEW['WIDTH'] = df_D5_N15_NEW.apply(lambda x: __cal_WIDTH(x), axis=1)
        def __cal_WIDTH_CODE(x):

            if x.WIDTH_CODE_N15 == '':
                rst = x.WIDTH_CODE_15
            else:
                rst = x.WIDTH_CODE_N15
            return rst

        df_D5_N15_NEW['WIDTH_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_WIDTH_CODE(x), axis=1)
        def __cal_THICK(x):

            if x.THICK_N15 == '':
                rst = x.THICK_15
            else:
                rst = x.THICK_N15
            return rst

        df_D5_N15_NEW['THICK'] = df_D5_N15_NEW.apply(lambda x: __cal_THICK(x), axis=1)
        def __cal_THICK_CODE(x):

            if x.THICK_CODE_N15 == '':
                rst = x.THICK_CODE_15
            else:
                rst = x.THICK_CODE_N15
            return rst

        df_D5_N15_NEW['THICK_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_THICK_CODE(x), axis=1)
        def __cal_ENTRY_WIDTH(x):

            if x.ENTRY_WIDTH_N15 == '':
                rst = x.ENTRY_WIDTH_15
            else:
                rst = x.ENTRY_WIDTH_N15
            return rst

        df_D5_N15_NEW['ENTRY_WIDTH'] = df_D5_N15_NEW.apply(lambda x: __cal_ENTRY_WIDTH(x), axis=1)
        def __cal_ENTRY_WIDTH_CODE(x):

            if x.ENTRY_WIDTH_CODE_N15 == '':
                rst = x.ENTRY_WIDTH_CODE_15
            else:
                rst = x.ENTRY_WIDTH_CODE_N15
            return rst

        df_D5_N15_NEW['ENTRY_WIDTH_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_ENTRY_WIDTH_CODE(x), axis=1)
        def __cal_ENTRY_THICK(x):

            if x.ENTRY_THICK_N15 == '':
                rst = x.ENTRY_THICK_15
            else:
                rst = x.ENTRY_THICK_N15
            return rst

        df_D5_N15_NEW['ENTRY_THICK'] = df_D5_N15_NEW.apply(lambda x: __cal_ENTRY_THICK(x), axis=1)
        def __cal_ENTRY_THICK_CODE(x):

            if x.ENTRY_THICK_CODE_N15 == '':
                rst = x.ENTRY_THICK_CODE_15
            else:
                rst = x.ENTRY_THICK_CODE_N15
            return rst

        df_D5_N15_NEW['ENTRY_THICK_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_ENTRY_THICK_CODE(x), axis=1)
        def __cal_PLAN_NO_C(x):

            if x.PLAN_NO_C_N15 == '':
                rst = x.PLAN_NO_C_15
            else:
                rst = x.PLAN_NO_C_N15
            return rst

        df_D5_N15_NEW['PLAN_NO_C'] = df_D5_N15_NEW.apply(lambda x: __cal_PLAN_NO_C(x), axis=1)
        def __cal_PLAN_NO_CODE(x):

            if x.PLAN_NO_CODE_N15 == '':
                rst = x.PLAN_NO_CODE_15
            else:
                rst = x.PLAN_NO_CODE_N15
            return rst

        df_D5_N15_NEW['PLAN_NO_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_PLAN_NO_CODE(x), axis=1)
        def __cal_TRIMMING_WIDTH_RANGE(x):

            if x.TRIMMING_WIDTH_RANGE_N15 == '':
                rst = x.TRIMMING_WIDTH_RANGE_15
            else:
                rst = x.TRIMMING_WIDTH_RANGE_N15
            return rst

        df_D5_N15_NEW['TRIMMING_WIDTH_RANGE'] = df_D5_N15_NEW.apply(lambda x: __cal_TRIMMING_WIDTH_RANGE(x), axis=1)
        def __cal_TRIMMING_WIDTH_CODE(x):

            if x.TRIMMING_WIDTH_CODE_N15 == '':
                rst = x.TRIMMING_WIDTH_CODE_15
            else:
                rst = x.TRIMMING_WIDTH_CODE_N15
            return rst

        df_D5_N15_NEW['TRIMMING_WIDTH_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_TRIMMING_WIDTH_CODE(x), axis=1)
        def __cal_IN_MAT_DIV_CODE(x):

            if x.IN_MAT_DIV_CODE_N15 == '':
                rst = x.IN_MAT_DIV_CODE_15
            else:
                rst = x.IN_MAT_DIV_CODE_N15
            return rst

        df_D5_N15_NEW['IN_MAT_DIV_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_IN_MAT_DIV_CODE(x), axis=1)
        def __cal_IN_MAT_DIV(x):

            if x.IN_MAT_DIV_N15 == '':
                rst = x.IN_MAT_DIV_15
            else:
                rst = x.IN_MAT_DIV_N15
            return rst

        df_D5_N15_NEW['IN_MAT_DIV'] = df_D5_N15_NEW.apply(lambda x: __cal_IN_MAT_DIV(x), axis=1)
        def __cal_PROD_CAT_CODE(x):

            if x.PROD_CAT_CODE_N15 == '':
                rst = x.PROD_CAT_CODE_15
            else:
                rst = x.PROD_CAT_CODE_N15
            return rst

        df_D5_N15_NEW['PROD_CAT_CODE'] = df_D5_N15_NEW.apply(lambda x: __cal_PROD_CAT_CODE(x), axis=1)
        def __cal_PROD_CAT(x):

            if x.PROD_CAT_N15 == '':
                rst = x.PROD_CAT_15
            else:
                rst = x.PROD_CAT_N15
            return rst

        df_D5_N15_NEW['PROD_CAT'] = df_D5_N15_NEW.apply(lambda x: __cal_PROD_CAT(x), axis=1)

        df_D5_N15_NEW.drop(['WIDTH_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['WIDTH_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['THICK_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['THICK_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_WIDTH_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_WIDTH_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_THICK_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_THICK_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PLAN_NO_C_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PLAN_NO_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['TRIMMING_WIDTH_RANGE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['TRIMMING_WIDTH_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['IN_MAT_DIV_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['IN_MAT_DIV_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PROD_CAT_CODE_15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PROD_CAT_15'], axis=1, inplace=True)

        df_D5_N15_NEW.drop(['WIDTH_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['WIDTH_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['THICK_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['THICK_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_WIDTH_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_WIDTH_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_THICK_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['ENTRY_THICK_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PLAN_NO_C_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PLAN_NO_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['TRIMMING_WIDTH_RANGE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['TRIMMING_WIDTH_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['IN_MAT_DIV_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['IN_MAT_DIV_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PROD_CAT_CODE_N15'], axis=1, inplace=True)
        df_D5_N15_NEW.drop(['PROD_CAT_N15'], axis=1, inplace=True)





        XRetryableSave(p_db_conn=self.db_conn_rds, p_table_name='T_ADS_FACT_SICB_DP0103', p_schema='BGTARAS1',
                       p_dataframe=df_D5_N15_NEW,
                       p_max_times=5).redo()

        # NOTE 286line就是最后一个截图

        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and WIDTH='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.WIDTH= " \
        #       " (select t2.WIDTH from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and WIDTH_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.WIDTH_CODE= " \
        #       " (select t2.WIDTH_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and THICK='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.THICK= " \
        #       " (select t2.THICK from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and THICK_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.THICK_CODE= " \
        #       " (select t2.THICK_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and ENTRY_WIDTH='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.ENTRY_WIDTH= " \
        #       " (select t2.ENTRY_WIDTH from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and ENTRY_WIDTH_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.ENTRY_WIDTH_CODE= " \
        #       " (select t2.ENTRY_WIDTH_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and ENTRY_THICK='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.ENTRY_THICK= " \
        #       " (select t2.ENTRY_THICK from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and ENTRY_THICK_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.ENTRY_THICK_CODE= " \
        #       " (select t2.ENTRY_THICK_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and PLAN_NO_C='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.PLAN_NO_C= " \
        #       " (select t2.PLAN_NO_C from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and PLAN_NO_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.PLAN_NO_CODE= " \
        #       " (select t2.PLAN_NO_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and TRIMMING_WIDTH_RANGE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.TRIMMING_WIDTH_RANGE= " \
        #       " (select t2.TRIMMING_WIDTH_RANGE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and TRIMMING_WIDTH_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.TRIMMING_WIDTH_CODE= " \
        #       " (select t2.TRIMMING_WIDTH_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end,self.account,self.unit,self.cost_center,self.data_type,self.account_period_start,self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and IN_MAT_DIV_CODE='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.IN_MAT_DIV_CODE= " \
        #       " (select t2.IN_MAT_DIV_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (
        #       self.account, self.unit, self.cost_center, self.data_type, self.account_period_start,
        #       self.account_period_end, self.account, self.unit, self.cost_center, self.data_type,
        #       self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and IN_MAT_DIV='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.IN_MAT_DIV= " \
        #       " (select t2.IN_MAT_DIV from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (
        #       self.account, self.unit, self.cost_center, self.data_type, self.account_period_start,
        #       self.account_period_end, self.account, self.unit, self.cost_center, self.data_type,
        #       self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and PROD_CAT_CODE=''  and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.PROD_CAT_CODE= " \
        #       " (select t2.PROD_CAT_CODE from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (
        #       self.account, self.unit, self.cost_center, self.data_type, self.account_period_start,
        #       self.account_period_end, self.account, self.unit, self.cost_center, self.data_type,
        #       self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)!='15' and PROD_CAT='' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " ) as t1 SET t1.PROD_CAT= " \
        #       " (select t2.PROD_CAT from( " \
        #       " select distinct PRODUCE_TIME,OUTPUT_WT,APPTHROWAIMODE,ENTRY_COILNO,PROD_COILNO,WIDTH,WIDTH_CODE,THICK,THICK_CODE,ENTRY_WIDTH,ENTRY_WIDTH_CODE,ENTRY_THICK,ENTRY_THICK_CODE,PLAN_NO_C,PLAN_NO_CODE,TRIMMING_WIDTH_RANGE,TRIMMING_WIDTH_CODE,IN_MAT_DIV_CODE,IN_MAT_DIV,PROD_CAT_CODE,PROD_CAT from BGTARAS1.T_ADS_FACT_SICB_DP0103 where left(cost_subject,2)='15' and acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' " \
        #       " )as t2 where t1.ENTRY_COILNO=t2.ENTRY_COILNO and t1.PROD_COILNO=t2.PROD_COILNO and t1.APPTHROWAIMODE =t2.APPTHROWAIMODE and t1.OUTPUT_WT=t2.OUTPUT_WT and t1.PRODUCE_TIME=t2.PRODUCE_TIME)" % (
        #       self.account, self.unit, self.cost_center, self.data_type, self.account_period_start,
        #       self.account_period_end, self.account, self.unit, self.cost_center, self.data_type,
        #       self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        # #select FIELD_LG,SPEC_BZ,RANGE_N,RANGE_COD,PROD1,LOWER_V,SUPER_V from BGRAGGCB.WH_JH00_ZSPEC2 where m_cent='MIDF00' and PROD1='S'
        # 更新所有的null为空字符串
        # df_D5['WIDTH'].fillna(value='', inplace=True)
        # df_D5['WIDTH_CODE'].fillna(value='', inplace=True)
        # df_D5['THICK'].fillna(value='', inplace=True)
        # df_D5['THICK_CODE'].fillna(value='', inplace=True)
        # df_D5['ENTRY_WIDTH'].fillna(value='', inplace=True)
        # df_D5['ENTRY_WIDTH_CODE'].fillna(value='', inplace=True)
        # df_D5['ENTRY_THICK'].fillna(value='', inplace=True)
        # df_D5['ENTRY_THICK_CODE'].fillna(value='', inplace=True)
        # df_D5['PLAN_NO_C'].fillna(value='', inplace=True)
        # df_D5['PLAN_NO_CODE'].fillna(value='', inplace=True)
        # df_D5['TRIMMING_WIDTH_RANGE'].fillna(value='', inplace=True)
        # df_D5['TRIMMING_WIDTH_CODE'].fillna(value='', inplace=True)
        # df_D5['IN_MAT_DIV_CODE'].fillna(value='', inplace=True)
        # df_D5['IN_MAT_DIV'].fillna(value='', inplace=True)
        # df_D5['PROD_CAT_CODE'].fillna(value='', inplace=True)
        # df_D5['PROD_CAT'].fillna(value='', inplace=True)

        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and WIDTH is null" \
        #       " ) as t1 SET t1.WIDTH=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and WIDTH_CODE is null" \
        #       " ) as t1 SET t1.WIDTH_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and THICK is null" \
        #       " ) as t1 SET t1.THICK=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and THICK_CODE is null" \
        #       " ) as t1 SET t1.THICK_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and ENTRY_WIDTH is null" \
        #       " ) as t1 SET t1.ENTRY_WIDTH=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and ENTRY_WIDTH_CODE is null" \
        #       " ) as t1 SET t1.ENTRY_WIDTH_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and ENTRY_THICK is null" \
        #       " ) as t1 SET t1.ENTRY_THICK=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and ENTRY_THICK_CODE is null" \
        #       " ) as t1 SET t1.ENTRY_THICK_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and PLAN_NO_C is null" \
        #       " ) as t1 SET t1.PLAN_NO_C=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and PLAN_NO_CODE is null" \
        #       " ) as t1 SET t1.PLAN_NO_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and TRIMMING_WIDTH_RANGE is null" \
        #       " ) as t1 SET t1.TRIMMING_WIDTH_RANGE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and TRIMMING_WIDTH_CODE is null" \
        #       " ) as t1 SET t1.TRIMMING_WIDTH_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and IN_MAT_DIV_CODE is null" \
        #       " ) as t1 SET t1.IN_MAT_DIV_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and IN_MAT_DIV is null" \
        #       " ) as t1 SET t1.IN_MAT_DIV=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and PROD_CAT_CODE is null" \
        #       " ) as t1 SET t1.PROD_CAT_CODE=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)
        #
        # sql = " UPDATE  ( " \
        #       " select * from BGTARAS1.T_ADS_FACT_SICB_DP0103 where acct='%s' and unit_code='%s' and cost_center='%s' and data_type='%s' and produce_time>='%s' and produce_time<'%s' and PROD_CAT is null" \
        #       " ) as t1 SET t1.PROD_CAT=''  " % (self.account, self.unit, self.cost_center, self.data_type, self.account_period_start, self.account_period_end)
        #
        # self.logger.info(sql)
        # self.db_conn_rds.execute(sql)


        util.closeConnection(self.db_conn_mpp2)

        # ???????
        # FIXME

        super(JHZKEYJob, self).do_execute()

    def __fill_spec_values_4_df_D5_via_c2(self, p_df_D5=None, p_row_d5=None, p_index=None, p_df_blue=None, p_tmp_qs_code=None, p_df_C2=None):
        """

        :param p_df_D5: big df
        :param p_index: the index of df_D5
        :param p_df_blue: 那个蓝色的图，实际就是spec_bz的数据
        :return:
        """
        # if p_tmp_qs_code is None or p_tmp_qs_code == '':
        #     p_tmp_qs_code = ''
        if p_tmp_qs_code != 'Q' and p_tmp_qs_code != 'S':
            p_tmp_qs_code = ''
        tmp_qs_code = p_tmp_qs_code
        tmp_m_cent = p_row_d5['M_CENT']

        for index_blue, row_blue in p_df_blue.iterrows():
            tmp_spec_bz = row_blue['SPEC_BZ']
            tmp_spec_show = row_blue['SPEC_SHOW']
            # p_df_C2['PROD1'].fillna(value='', inplace=True)

            def __cal_QS_CODE(x):
                rst = ''

                if x.PROD1 != 'Q' and x.PROD1 != 'S':
                    rst = ''
                else:
                    rst = x.PROD1

                return rst


            p_df_C2['QS_CODE'] = p_df_C2.apply(lambda x: __cal_QS_CODE(x), axis=1)
            df_C2_t = p_df_C2[
                (p_df_C2['SPEC_BZ'] == tmp_spec_bz) & (p_df_C2['M_CENT'] == tmp_m_cent) & (p_df_C2['QS_CODE'] == tmp_qs_code)]
            # df_C2_t = p_df_C2[
            #     (p_df_C2['SPEC_BZ'] == tmp_spec_bz) & (p_df_C2['M_CENT'] == tmp_m_cent)]
            success = df_C2_t.empty is False
            self.logger.info(success)
            if success is False:
                # df_C2_t = p_df_C2[
                #     (p_df_C2['SPEC_BZ'] == tmp_spec_bz) & (p_df_C2['M_CENT'] == tmp_m_cent) & (
                #                 p_df_C2['PROD1'] == '')]
                df_C2_t = p_df_C2[
                    (p_df_C2['SPEC_BZ'] == tmp_spec_bz) & (p_df_C2['M_CENT'] == tmp_m_cent)]
                # continue
            success = df_C2_t.empty is False
            if success is False:
                continue

            df_C2_t = df_C2_t.reset_index()
            field_lg_property = df_C2_t.loc[0, 'FIELD_LG']
            #
            #sd = SD4C2(p_db_conn=self.db_conn_dbprod7)
            # so = SO4C2(p_db_conn=self.db_conn_dbprod7, p_spec_bz=tmp_spec_bz, p_m_cent=tmp_m_cent,
            #            p_qs_code=tmp_qs_code)
            # if so.success is False:
            #     so = SO4C2(p_db_conn=self.db_conn_dbprod7, p_spec_bz=tmp_spec_bz, p_m_cent=tmp_m_cent,
            #                p_qs_code=None)

            #success, field_lg_property = sd.get_all_c2_by(p_spec_bz=tmp_spec_bz,
            #                                              p_m_cent=tmp_m_cent,
            #                                              p_qs_code=tmp_qs_code)
            #success, field_lg_property = so.get_all_c2_by()

            if success is False:
                continue

            field_lg_value = p_df_D5.loc[p_index, field_lg_property]
            # success, row_c2, field_lg_property, range_code, range_n \
            #     = so.get_matched_c2_by(p_field_lg_value=field_lg_value)
            #success, row_c2, field_lg_property, range_code, range_n \
            #    = sd.get_matched_c2_by(p_spec_bz=tmp_spec_bz,
            #                           p_m_cent=tmp_m_cent,
            #                           p_qs_code=tmp_qs_code,
            #                           p_field_lg_value=field_lg_value)

            if field_lg_property is not None:
                for index, row in df_C2_t.iterrows():
                    if field_lg_value >= float(row['LOWER_V']) and field_lg_value < float(row['SUPER_V']):
                        t = (True, row, field_lg_property, row['RANGE_COD'], row['RANGE_N'])
                        row_c2 = row
                        range_code = row['RANGE_COD']
                        range_n = row['RANGE_N']
                        break
            # self.logger.info(range_code)
            # self.logger.info(range_n)

            # 定位到行，定位到列
            p_df_D5.loc[p_index, tmp_spec_bz] = range_code if success else None
            p_df_D5.loc[p_index, tmp_spec_show] = range_n if success else None
            #self.logger.info(p_df_D5)


    def __query_blue_pic(self, p_item_id=None, p_proj_ty=None):
        # NOTE 如果有值， 则根据item_id去查出那个蓝色的图
        sql = " SELECT " \
              " a.ITEM_ID," \
              " a.SPEC_BZ, " \
              " a.SPEC_SHOW " \
              " FROM " \
              " M1_JH.WH_JH00_ZSPEC1 as a " \
              " WHERE 1=1 " \
              " AND a.ACCOUNT='%s' " \
              " AND item_id='%s' " \
              " AND a.USE_JH='Y' " \
              " AND substr(a.ITEM_ID,7,2) IN ('SD','%s','WZ') " \
              " ORDER BY " \
              " a.SPEC_BZ" % (self.account,
                                       p_item_id,
                                       p_proj_ty)
        #df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        success = df.empty is False
        return success, df

    def __query_ec3_by(self, p_item_id=None, p_spec=None):
        """

        :param p_item_id:
        :param p_spec:
        :return:
        """
        sql = " SELECT * " \
              " FROM M1_JH.WH_JH00_ZSPEC3   " \
              " WHERE 1=1 " \
              " AND SPEC='%s' " \
              " AND ITEM_ID='%s' " % (p_spec, p_item_id)
        self.logger.info(sql)
        #df = XRetryableQuery(p_db_conn=self.db_conn_dbprod7, p_sql=sql, p_max_times=5).redo()
        df = XRetryableQuery(p_db_conn=self.db_conn_mpp2, p_sql=sql, p_max_times=5).redo()
        df.columns = df.columns.str.upper()
        success = df.empty is False
        return success, df

    def __cal_spec_via_c3(self, x=None, p_c3=None):
        """
        它完全是动态的 我以前一直在QS_CODE里出不来， 没理解到它完全是动态这个点
        就连那三个函数 也是动态的
        :param x: df_D5 row object
        :param p_c3: C3 object
        :return:
        """
        # PLAN_NO_COD foo_spec_eval_no_PLAN_NO_COD
        # PLAN_NO_C foo_spec_show_eval_no_PLAN_NO_C
        # JHZ_KEY -- foo_spec_eval_no_PLAN_NO_CODSIW9631J1
        #self.logger.info('__cal_SPEC ---- %s ---- %s ---- %s' % (x.PLAN_NO_COD, p_c3.logic, p_c3.py_code))

        if p_c3.spec == 'PLAN_NO_COD':
            self.logger.info('??')

        targeted = False
        if p_c3.has_db2_sql_code:
            try:
                # NOTE 这个eval实际是针对df_D5的row数据, 只是这个code是存在c3内而已。是操作df_D5的row数据。
                yes = eval(p_c3.py_code)
                success = True
                msg = ''
            except Exception as e:
                success = False
                msg = str(e)
            if success:
                if yes:
                    spec_value = p_c3.value
                    spec_show_value = p_c3.value_name
                    targeted = True
                else:
                    spec_value = 'foo_spec_eval_no_%s' % (p_c3.spec)
                    spec_show_value = 'foo_spec_show_eval_no_%s' % (p_c3.spec_show)
            else:
                spec_value = 'foo_spec_eval_fail_%s_%s' % (p_c3.spec, msg)
                spec_show_value = 'foo_spec_show_eval_fail_%s_%s' % (p_c3.spec_show, msg)
        else:
            spec_value = 'foo_no_db2_sql_code_%s' % (p_c3.spec)
            spec_show_value = 'foo_no_db2_sql_code_%s' % (p_c3.spec_show)
        return targeted, spec_value, spec_show_value
